﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QN_DiemSV.Forms
{
    public partial class frmTTSV : Form
    {
        public frmTTSV()
        {
            InitializeComponent();
        }
        bool them = false;
        bool sua = false;
        Classes.connect conn = new QN_DiemSV.Classes.connect();
        void VisionControl()
        {
            mskNgaySinh.Enabled = true;
            txtMaSV.Enabled = true;
            txtHodem.Enabled = true;
            txtTen.Enabled = true;
            cboMalop.Enabled = true;
            cboGioiTinh.Enabled = true;
            cboMaKhoa.Enabled = true;
        }
        void EnableControl()
        {
            mskNgaySinh.Enabled = false;
            txtMaSV.Enabled = false;
            txtHodem.Enabled = false;
            txtTen.Enabled = false;
            cboMalop.Enabled = false;
            cboGioiTinh.Enabled = false;
            cboMaKhoa.Enabled = false;
        }
        void LoadToCombobox(ComboBox cbo, string sql)
        {
            DataTable dt = conn.excuteSelect(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                cbo.Items.Add(dt.Rows[i][0].ToString());
            }
        }
        void LoadData()
        {
            DataTable dt = new DataTable();
            dt = conn.excuteSelect("select * from SINhVIEN");
            dgrDSSV.DataSource = dt;
        }
        private void groupBox2_Enter(object sender, EventArgs e)
        {

        }

        private void frmTTSV_Load(object sender, EventArgs e)
        {
            EnableControl();
            LoadData();
            LoadToCombobox(cboKhoahoc, "select TenKhoa from KHOA");
            LoadToCombobox(cboLop, "select TenLop from LOP");
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            them = true;
            VisionControl();
            txtMaSV.Focus();
            LoadToCombobox(cboMaKhoa, "select  MaKhoa from KHOA");
            LoadToCombobox(cboMalop, "select MaLop from LOP");
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            LoadToCombobox(cboMaKhoa, "select  MaKhoa from KHOA");
            LoadToCombobox(cboMalop, "select MaLop from LOP");
            sua = true;
            VisionControl();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (them == true)
            {
                conn.EXE_PROC_PARAMS("SinhVienInsert", "@MaSV", int.Parse(txtMaSV.Text), "@Hodem", txtHodem.Text, "@Ten", txtTen.Text, "@NgaySinh", Convert.ToDateTime(mskNgaySinh.Text), "@GioiTinh", Convert.ToBoolean(cboGioiTinh.SelectedIndex), "@DiaChi", txtDiaChi.Text, "@MaLop", int.Parse(cboMalop.Text), "@MaKhoa", cboMaKhoa.Text);
                LoadData();
            }
            if (sua == true)
            {
                DateTime dt = DateTime.Parse(mskNgaySinh.Text);
                conn.excuteNonquery("update SINHVIEN set Hodem =N'" + txtHodem.Text + "', Ten=N'" + txtTen.Text + "', NgaySinh='" + dt.ToShortDateString() + "', GioiTinh='" + Convert.ToBoolean(cboGioiTinh.SelectedIndex) + "', DiaChi=N'" + txtDiaChi.Text + "',MaLop=" + int.Parse(cboMalop.Text) + ", MaKhoa='" + cboMaKhoa.Text + "' where MaSV=" + int.Parse(txtMaSV.Text.Trim()));
                LoadData();
            }
        }

        private void dgrDSSV_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            LoadToCombobox(cboKhoahoc, "select TenKhoa from KHOA");
            LoadToCombobox(cboLop, "select TenLop from LOP");
            int index = e.RowIndex;
            txtMaSV.Text = dgrDSSV.Rows[index].Cells["MaSV"].Value.ToString();
            txtHodem.Text = dgrDSSV.Rows[index].Cells["Hodem"].Value.ToString();
            txtTen.Text = dgrDSSV.Rows[index].Cells["Ten"].Value.ToString();
            txtDiaChi.Text = dgrDSSV.Rows[index].Cells["DiaChi"].Value.ToString();
            cboMaKhoa.Text = dgrDSSV.Rows[index].Cells["MaKhoa"].Value.ToString();
            cboMalop.Text = dgrDSSV.Rows[index].Cells["MaLop"].Value.ToString();
            mskNgaySinh.Text = dgrDSSV.Rows[index].Cells["NgaySinh"].Value.ToString();
            string gt = dgrDSSV.Rows[index].Cells["GioiTinh"].Value.ToString();
            if (gt == "0")
                cboGioiTinh.Text = "Nam";
            else
                cboGioiTinh.Text = "Nữ";

        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            conn.excuteNonquery("delete from SINHVIEN where MaSV='" + txtMaSV.Text + "'");
            LoadData();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            string maK = cboKhoahoc.Text;
            string maLop = cboLop.Text;
            string sql = "select * from SINHVIEN sv";
            DataTable dt = new DataTable();
            if (cboKhoahoc.Text == "" && cboLop.Text=="")
            {
                MessageBox.Show("Bạn chưa chọn Khoa hoặc Lớp");
            }
            if (cboLop.Text!="" && cboKhoahoc.Text =="")
            {
                sql += " where sv.MaLop= (select MaLop from LOP where TenLop='" + cboLop.Text + "')";
                dt = conn.excuteSelect(sql);

            }
            else if (cboLop.Text=="" && cboKhoahoc.Text !="")
            {
                sql += " where sv.MaKhoa= (select MaKhoa from KHOA where TenKhoa='" + cboKhoahoc.Text + "')";
                dt = conn.excuteSelect(sql);
            }
            else if (cboLop.Text!="" && cboKhoahoc.Text !="")
            {
                sql += "where sv.MaKhoa= (select MaKhoa from KHOA where TenKhoa='" + cboKhoahoc.Text + "') and  sv.MaLop= (select MaLop from LOP where TenLop='" + cboLop.Text + "')";
                dt = conn.excuteSelect(sql);
            }
            dgrDSSV.DataSource = null;
            dgrDSSV.DataSource = dt;

        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
